Skip Headers

Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 5 Authorization: Privileges, Roles, Profiles, and Resource Limitations

Authorization includes primarily two processes:

- Permitting only certain users to access, process, or alter data

- Applying varying limitations on user access or actions. The limitations placed on (or removed from) users can apply to objects, such as schemas, tables, or rows; or to resources, such as time (CPU, connect, or idle times).

This chapter introduces the basic concepts and mechanisms for placing or removing such limitations on users, individually or in groups, in the following sections:

Topic Category | Links to Topics
--- | ---
How Privileges Are Acquired and Used | Introduction to Privileges, including system, schema, object, table, procedure, and other privileges
How Roles Are Acquired, Used, and Restricted | Introduction to Roles
How and Why Resource Limits Are Applied to Users | User Resource Limits
How Profiles Are Determined and Used | Profiles

See Also: Chapter 11, "Administering User Privileges, Roles, and Profiles", discusses how to configure and administer privileges, roles, and profiles for users, including DBAs and application programmers.

## 5.1 Introduction to Privileges

A privilege is a right to execute a particular type of SQL statement or to access another user's object. Some examples of privileges include the right to:

- Connect to the database (create a session)

- Create a table

- Select rows from another user's table

- Execute another user's stored procedure

You grant privileges to users so these users can accomplish tasks required for their jobs. You should grant a privilege only to a user who requires that privilege to accomplish the necessary work. Excessive granting of unnecessary privileges can compromise security. A user can receive a privilege in two different ways:

- You can grant privileges to users explicitly. For example, you can explicitly grant to user `SCOTT` the privilege to insert records into the `employees` table.

- You can also grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example, you can grant the privileges to select, insert, update, and delete records from the `employees` table to the role named `clerk`, which in turn you can grant to users `scott` and `brian`.

Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users.

See Also: Chapter 11, "Administering User Privileges, Roles, and Profiles" Oracle Database Administrator's Guide for discussions of managing and using system and schema object privileges Oracle Database SQL Reference for the complete list of system privileges and their descriptions

There are six major categories of privileges, some with significant subcategories:

- System Privileges

- Schema Object Privileges

- Table Privileges

- View Privileges

- Procedure Privileges

- Type Privileges

### 5.1.1 System Privileges

A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 100 distinct system privileges to manage as described in the following subsections:

- Granting and Revoking System Privileges

- Who Can Grant or Revoke System Privileges?

#### 5.1.1.1 Granting and Revoking System Privileges

You can grant or revoke system privileges to users and roles. If you grant system privileges to roles, then you can use the roles to manage system privileges. For example, roles permit privileges to be made selectively available.

Note: In general, you grant system privileges only to administrative personnel and application developers. End users normally do not require and should not have the associated capabilities.

Use either of the following to grant or revoke system privileges to users and roles:

- The Oracle Enterprise Manager 10g Database Control

- The `GRANT` and `REVOKE` SQL statements

  See Also: For more information about Database Control, see Oracle Database 2 Day DBA. For information about modifying users with Database Control, see the topic "Creating, Editing, and Deleting Users" in the Enterprise Manager online help.

#### 5.1.1.2 Who Can Grant or Revoke System Privileges?

Only two types of users can grant system privileges to other users or revoke such privileges from them:

- Users who have been granted a specific system privilege with the `ADMIN` `OPTION`

- Users with the system privilege `GRANT` `ANY` `PRIVILEGE`

### 5.1.2 Schema Object Privileges

A schema object privilege is the permission to perform a particular action on a specific schema object.

Different object privileges are available for different types of schema objects. The privilege to delete rows from the `departments` table is an example of an object privilege.

Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges. Their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the `ALTER` `ANY` `CLUSTER` system privilege.

The following subsections discuss granting and revoking such privileges:

- Granting and Revoking Schema Object Privileges

- Who Can Grant Schema Object Privileges?

- Using Privileges with Synonyms

Object privileges that apply to specific schema objects are discussed in the following sections:

- Table Privileges

- View Privileges

- Sequences (see Managing Sequences in Oracle Database Administrator's Guide

- Procedure Privileges

- Functions and Packages (Managing Object Dependencies in Oracle Database Administrator's Guide)

- Type Privileges

#### 5.1.2.1 Granting and Revoking Schema Object Privileges

Schema object privileges can be granted to and revoked from users and roles. If you grant object privileges to roles, then you can make the privileges selectively available.

Object privileges for users and roles can be granted or revoked using the following:

- The SQL statements `GRANT` and `REVOKE`

- The Oracle Enterprise Manager 10g Database Control

  See Also: For more information about Database Control, see Oracle Database 2 Day DBA. For information about modifying privileges with Database Control, see the Enterprise Manager online help.

#### 5.1.2.2 Who Can Grant Schema Object Privileges?

A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. A user with the `GRANT` `ANY` `OBJECT` `PRIVILEGE` can grant or revoke any specified object privilege to another user with or without the `GRANT` `OPTION` of the `GRANT` statement. Otherwise, the grantee can use the privilege, but cannot grant it to other users.

For example, assume user `SCOTT` owns a table named `t2`:

    SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1;
    SQL> CONNECT u1/u1
    Connected.
    SQL> GRANT SELECT ON scott.t2 TO u2;
    SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
     WHERE TABLE_NAME = 'employees';

    GRANTEE                        OWNER
    ------------------------------ ------------------------------
    GRANTOR                        PRIVILEGE                                GRA
    ------------------------------ ---------------------------------------- ---
    U2                             SCOTT
    SCOTT                          SELECT                                   NO

See Also: Oracle Database SQL Reference

#### 5.1.2.3 Using Privileges with Synonyms

A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or by using a synonym.

For example, assume there is a table `jward.emp` with a synonym named `jward.employee`, and the user `jward` issues the following statement:

    GRANT SELECT ON emp TO swilliams;

The user `swilliams` can query `jward.emp` by referencing the table by name or by using the synonym `jward.employee`:

    SELECT * FROM jward.emp;
    SELECT * FROM jward.employee;

If you grant object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object, then the effect is the same as if no synonym were used. For example, if `jward` wanted to grant the `SELECT` privilege for the `emp` table to `swilliams`, then `jward` could issue either of the following statements:

    GRANT SELECT ON emp TO swilliams;
    GRANT SELECT ON employee TO swilliams;

If a synonym is dropped, then all grants for the underlying schema object remain in effect, even if the privileges were granted by specifying the dropped synonym.

### 5.1.3 Table Privileges

Schema object privileges for tables enable table security at the Data Manipulation Language (DML) or Data Definition Language (DDL) level of operation, as discussed in the following subsections:

- DML Operations

- DDL Operations

#### 5.1.3.1 DML Operations

You can grant privileges to use the `DELETE`, `INSERT`, `SELECT`, and `UPDATE` DML operations on a table or view. Grant these privileges only to users and roles that need to query or manipulate data in a table.

You can restrict `INSERT` and `UPDATE` privileges for a table to specific columns of the table. With selective `INSERT`, a privileged user can insert a row with values for the selected columns. All other columns receive `NULL` or the default value of the column. With selective `UPDATE`, a user can update only specific column values of a row. Selective `INSERT` and `UPDATE` privileges are used to restrict user access to sensitive data.

For example, if you do not want data entry users to alter the `salary` column of the `employees` table, then selective `INSERT` or `UPDATE` privileges can be granted that exclude the `salary` column. Alternatively, a view that excludes the `salary` column could satisfy this need for additional security.

See Also: Oracle Database SQL Reference for more information about DML operations

#### 5.1.3.2 DDL Operations

The `ALTER`, `INDEX`, and `REFERENCES` privileges allow DDL operations to be performed on a table. Because these privileges allow other users to alter or create dependencies on a table, you should grant privileges conservatively.

A user attempting to perform a DDL operation on a table may need additional system or object privileges. For example, to create a trigger on a table, the user requires both the `ALTER` `TABLE` object privilege for the table and the `CREATE` `TRIGGER` system privilege.

As with the `INSERT` and `UPDATE` privileges, the `REFERENCES` privilege can be granted on specific columns of a table. The `REFERENCES` privilege enables the grantee to use the table on which the grant is made as a parent key to any foreign keys that the grantee wishes to create in his or her own tables. This action is controlled with a special privilege because the presence of foreign keys restricts the data manipulation and table alterations that can be done to the parent key. A column-specific `REFERENCES` privilege restricts the grantee to using the named columns (which, of course, must include at least one primary or unique key of the parent table).

See Also: Data Integrity in Oracle Database Concepts for more information about primary keys, unique keys, and integrity constraints

### 5.1.4 View Privileges

A view is a presentation of data selected from one or more tables (possibly including other views). A view shows the structure of the underlying tables as well as the selected data, and can be thought of as the result of a stored query. The view contains no actual data but rather derives what it shows from the tables and views on which it is based. A view can be queried, and the data it represents can be changed. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based and are subject to the integrity constraints and triggers of the base tables.

DML object privileges for tables can be applied similarly to views. Schema object privileges for a view allow various DML operations, which as noted affect the base tables from which the view is derived. These privileges are discussed in the following subsections:

- Privileges Required to Create Views

- Increasing Table Security with Views

#### 5.1.4.1 Privileges Required to Create Views

To create a view, you must meet the following requirements:

- You must have been granted one of the following system privileges, either explicitly or through a role:

  - The `CREATE` `VIEW` system privilege (to create a view in your schema)

  - The `CREATE` `ANY` `VIEW` system privilege (to create a view in another user's schema)

- You must have been explicitly granted one of the following privileges:

  - The `SELECT`, `INSERT`, `UPDATE`, or `DELETE` object privileges on all base objects underlying the view

  - Th`e` `SELECT` `ANY` `TABLE`, `INSERT` `ANY` `TABLE`, `UPDATE` `ANY` `TABLE`, or `DELETE` `ANY` `TABLE` system privileges

- In addition, in order to grant other users access to your view, you must have received object privileges to the base objects with the `GRANT` `OPTION` clause or appropriate system privileges with the `ADMIN` `OPTION` clause. If you have not, then grantees cannot access your view.

  See Also: Oracle Database SQL Reference

#### 5.1.4.2 Increasing Table Security with Views

To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base objects underlying the view.

Views add two more levels of security for tables, column-level security and value-based security:

- A view can provide access to selected columns of base tables. For example, you can define a view on the `employees` table to show only the `employee_id`, `last_name`, and `manager_id` columns:

      CREATE VIEW employees_manager AS
          SELECT last_name, employee_id, manager_id FROM employees;

- A view can provide value-based security for the information in a table. A `WHERE` clause in the definition of a view displays only selected rows of base tables. Consider the following two examples:

      CREATE VIEW lowsal AS
          SELECT * FROM employees
          WHERE salary < 10000;

  The `LOWSAL` view allows access to all rows of the `employees` table that have a salary value less than 10000. Notice that all columns of the `employees` table are accessible in the `LOWSAL` view.

      CREATE VIEW own_salary AS
          SELECT last_name, salary
          FROM employees
          WHERE last_name = USER;

  In the `own_salary` view, only the rows with an `last_name` that matches the current user of the view are accessible. The `own_salary` view uses the `user` pseudocolumn, whose values always refer to the current user. This view combines both column-level security and value-based security.

### 5.1.5 Procedure Privileges

`EXECUTE` is the only schema object privilege for procedures, including standalone procedures and functions as well as packages. Grant this privilege only to users who need to execute a procedure or to compile another procedure that calls a desired procedure. To create and manage secure and effective use of procedure privileges, you need to understand the following subsections:

- Procedure Execution and Security Domains

- System Privileges Needed to Create or Alter a Procedure

- Packages and Package Objects

#### 5.1.5.1 Procedure Execution and Security Domains

A user with the `EXECUTE` object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure. No run-time privilege check is made when the procedure is called. A user with the `EXECUTE` `ANY` `PROCEDURE` system privilege can execute any procedure in the database. Privileges to execute procedures can be granted to a user through roles.

The owner of a procedure, called the definer, must have all the necessary object privileges for referenced objects. If the owner grants to another user the right to use that procedure, then the owner object privileges for the objects referenced by the procedure apply to that user's exercise of the procedure. These are termed "definer's rights."

The user of a procedure who is not its owner is called the "invoker." Additional privileges on referenced objects are required for invoker's rights procedures, but not for definer's rights procedures.

See Also: "PL/SQL Blocks and Roles"

Definer's Rights

A user of a definer's rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses, because a definer's rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The owner of the procedure must have all the necessary object privileges for referenced objects. Fewer privileges have to be granted to users of a definer's rights procedure, resulting in tighter control of database access.

You can use definer's rights procedures to control access to private database objects and add a level of database security. By writing a definer's rights procedure and granting only `EXECUTE` privilege to a user, the user can be forced to access the referenced objects only through the procedure.

At run-time, the privileges of the owner of a definer's rights stored procedure are always checked before the procedure is executed. If a necessary privilege on a referenced object has been revoked from the owner of a definer's rights procedure, then the procedure cannot be executed by the owner or any other user.

Note: Trigger execution follows the same patterns as definer's rights procedures. The user executes a SQL statement, which that user is privileged to execute. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger.

See Also: "Triggers" in Oracle Database Concepts

Invoker's Rights

An invoker's rights procedure executes with all of the invoker's privileges. Roles are enabled unless the invoker's rights procedure was called directly or indirectly by a definer's rights procedure. A user of an invoker's rights procedure needs privileges (either directly or through a role) on objects that the procedure accesses through external references that are resolved in the invoker's schema.

The invoker needs privileges at run-time to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at run-time.

For all other external references, such as direct PL/SQL function calls, the owner's privileges are checked at compile time, and no run-time check is made. Therefore, the user of an invoker's rights procedure needs no privileges on external references outside DML or dynamic SQL statements. Alternatively, the developer of an invoker's rights procedure only needs to grant privileges on the procedure itself, not on all objects directly referenced by the invoker's rights procedure.

You can create a software bundle that consists of multiple program units, some with definer's rights and others with invoker's rights, and restrict the program entry points (controlled step-in). A user who has the privilege to execute an entry-point procedure can also execute internal program units indirectly, but cannot directly call the internal programs.

See Also: "Introduction to Fine-Grained Access Control" PL/SQL Packages and Types Reference for detailed documentation of the Oracle supplied packages

#### 5.1.5.2 System Privileges Needed to Create or Alter a Procedure

To create a procedure, a user must have the `CREATE` `PROCEDURE` or `CREATE` `ANY` `PROCEDURE` system privilege. To alter a procedure, that is, to manually recompile a procedure, a user must own the procedure or have the `ALTER` `ANY` `PROCEDURE` system privilege.

The user who owns the procedure also must have privileges for schema objects referenced in the procedure body. To create a procedure, you must have been explicitly granted the necessary privileges (system or object) on all objects referenced by the procedure. You cannot have obtained the required privileges through roles. This includes the `EXECUTE` privilege for any procedures that are called inside the procedure being created.

Note: Triggers also require that privileges to referenced objects be granted explicitly to the trigger owner. Anonymous PL/SQL blocks can use any privilege, whether the privilege is granted explicitly or through a role.

#### 5.1.5.3 Packages and Package Objects

A user with the `EXECUTE` object privilege for a package can execute any public procedure or function in the package and access or modify the value of any public package variable. Specific `EXECUTE` privileges cannot be granted for individual constructs in a package. Therefore, you may find it useful to consider two alternatives for establishing security when developing procedures, functions, and packages for a database application. These alternatives are described in the following examples.

Packages and Package Objects: Example 1

This example shows four procedures created in the bodies of two packages.

    CREATE PACKAGE BODY hire_fire AS
      PROCEDURE hire(...) IS
        BEGIN
          INSERT INTO employees . . .
        END hire;
      PROCEDURE fire(...) IS
        BEGIN
          DELETE FROM employees . . .
        END fire;
    END hire_fire;

    CREATE PACKAGE BODY raise_bonus AS
      PROCEDURE give_raise(...) IS
        BEGIN
          UPDATE employees SET salary = . . .
        END give_raise;
      PROCEDURE give_bonus(...) IS
        BEGIN
          UPDATE employees SET bonus = . . .
        END give_bonus;
    END raise_bonus;

Access to execute the procedures is given by granting the `EXECUTE` privilege for the package by using the following statements:

    GRANT EXECUTE ON hire_fire TO big_bosses;
    GRANT EXECUTE ON raise_bonus TO little_bosses;

Note: Granting `EXECUTE` privilege granted for a package provides uniform access to all package objects.

Packages and Package Objects: Example 2

This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.

    CREATE PACKAGE BODY employee_changes AS
      PROCEDURE change_salary(...) IS BEGIN ... END;
      PROCEDURE change_bonus(...) IS BEGIN ... END;
      PROCEDURE insert_employee(...) IS BEGIN ... END;
      PROCEDURE delete_employee(...) IS BEGIN ... END;
    END employee_changes;

    CREATE PROCEDURE hire
      BEGIN
        employee_changes.insert_employee(...)
      END hire;

    CREATE PROCEDURE fire
      BEGIN
        employee_changes.delete_employee(...)
      END fire;

    PACKAGE raise_bonus IS
      PROCEDURE give_raise(...) AS
        BEGIN
          employee_changes.change_salary(...)
        END give_raise;

      PROCEDURE give_bonus(...)
        BEGIN
          employee_changes.change_bonus(...)
        END give_bonus;

Using this method, the procedures that actually do the work (the procedures in the `employee_changes` package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring top-level procedures, `hire` and `fire`, and an additional package, `raise_bonus`, you can grant selective `EXECUTE` privileges on procedures in the main package:

    GRANT EXECUTE ON hire, fire TO big_bosses;
    GRANT EXECUTE ON raise_bonus TO little_bosses;

### 5.1.6 Type Privileges

The following subsections describe the use of privileges for types, methods, and objects:

- System Privileges for Named Types

- Object Privileges

- Method Execution Model

- Privileges Required to Create Types and Tables Using Types

- Example of Privileges for Creating Types and Tables Using Types

- Privileges on Type Access and Object Access

- Type Dependencies

#### 5.1.6.1 System Privileges for Named Types

Oracle defines system privileges shown in Table 5-1 for named types (object types, `VARRAY`s, and nested tables):

Table 5-1 System Privileges for Named Types

Privilege | Allows you to...
--- | ---
`CREATE TYPE` | Create named types in your own schemas
`CREATE ANY TYPE` | Create a named type in any schema
`ALTER ANY TYPE` | Alter a named type in any schema
`DROP ANY TYPE` | Drop a named type in any schema
`EXECUTE ANY TYPE` | Use and reference a named type in any schema

The `RESOURCE` role includes the `CREATE` `TYPE` system privilege. The DBA role includes all of these privileges.

#### 5.1.6.2 Object Privileges

The only object privilege that applies to named types is `EXECUTE`. If the `EXECUTE` privilege exists on a named type, then a user can use the named type to:

- Define a table

- Define a column in a relational table

- Declare a variable or parameter of the named type

The `EXECUTE` privilege permits a user to invoke the methods in the type, including the type constructor. This is similar to `EXECUTE` privilege on a stored PL/SQL procedure.

#### 5.1.6.3 Method Execution Model

Method execution is the same as any other stored PL/SQL procedure.

See Also: "Procedure Privileges"

#### 5.1.6.4 Privileges Required to Create Types and Tables Using Types

To create a type, you must meet the following requirements:

- You must have the `CREATE` `TYPE` system privilege to create a type in your schema or the `CREATE` `ANY` `TYPE` system privilege to create a type in the schema of another user. These privileges can be acquired explicitly or through a role.

- The owner of the type must be explicitly granted the `EXECUTE` object privileges to access all other types referenced within the definition of the type, or have been granted the `EXECUTE` `ANY` `TYPE` system privilege. The owner cannot have obtained the required privileges through roles.

- If the type owner intends to grant access to the type to other users, then the owner must have received the `EXECUTE` privileges to the referenced types with the `GRANT` `OPTION` or the `EXECUTE` `ANY` `TYPE` system privilege with the `ADMIN` `OPTION`. If not, then the type owner has insufficient privileges to grant access on the type to other users.

To create a table using types, you must meet the requirements for creating a table and the following additional requirements:

- The owner of the table must have been explicitly granted the `EXECUTE` object privileges to access all types referenced by the table, or have been granted the `EXECUTE` `ANY` `TYPE` system privilege. The owner cannot have obtained the required privileges through roles.

- If the table owner intends to grant access to the table to other users, then the owner must have received the `EXECUTE` privileges to the referenced types with the `GRANT` `OPTION` or the `EXECUTE` `ANY` `TYPE` system privilege with the `ADMIN` `OPTION`. If not, then the table owner has insufficient privileges to grant access on the type to other users.

  See Also: "Table Privileges" for the requirements for creating a table

#### 5.1.6.5 Example of Privileges for Creating Types and Tables Using Types

Assume that three users exist with the `CONNECT` and `RESOURCE` roles:

- `user1`

- `user2`

- `user3`

`User1` performs the following DDL in his schema:

    CREATE TYPE type1 AS OBJECT (
      attr1 NUMBER);

    CREATE TYPE type2 AS OBJECT (
      attr2 NUMBER);

    GRANT EXECUTE ON type1 TO user2;
    GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;

`User2` performs the following DDL in his schema:

    CREATE TABLE tab1 OF user1.type1;
    CREATE TYPE type3 AS OBJECT (
      attr3 user1.type2);
    CREATE TABLE tab2 (
      col1 user1.type2);

The following statements succeed because `user2` has `EXECUTE` privilege on `user1.type2` with the `GRANT` `OPTION:`

    GRANT EXECUTE ON type3 TO user3;
    GRANT SELECT on tab2 TO user3;

However, the following grant fails because `user2` does not have `EXECUTE` privilege on `user1.type1` with the `GRANT` `OPTION:`

    GRANT SELECT ON tab1 TO user3;

`User3` can successfully perform the following statements:

    CREATE TYPE type4 AS OBJECT (
      attr4 user2.type3);
    CREATE TABLE tab3 OF type4;

Note: Customers should discontinue using the `CONNECT` and `RESOURCE` roles, as they will be deprecated in future Oracle Database releases. The `CONNECT` role presently retains only the `CREATE SESSION` privilege.

#### 5.1.6.6 Privileges on Type Access and Object Access

Existing column-level and table-level privileges for DML statements apply to both column objects and row objects. Oracle defines the privileges shown in Table 5-2 for object tables.

Table 5-2 Privileges for Object Tables

Privilege | Allows you to...
--- | ---
`SELECT` | Access an object and its attributes from the table
`UPDATE` | Modify the attributes of the objects that make up the rows in the table
`INSERT` | Create new objects in the table
`DELETE` | Delete rows

Similar table privileges and column privileges apply to column objects. Retrieving instances does not in itself reveal type information. However, clients must access named type information in order to interpret the type instance images. When a client requests such type information, Oracle Database checks for `EXECUTE` privilege on the type.

Consider the following schema:

    CREATE TYPE emp_type (
        eno NUMBER, ename CHAR(31), eaddr addr_t);
    CREATE TABLE emp OF emp_t;

In addition, consider the following two queries:

    SELECT VALUE(emp) FROM emp;
    SELECT eno, ename FROM emp;

For either query, Oracle Database checks the `SELECT` privilege of the user for the `emp` table. For the first query, the user needs to obtain the `emp_type` type information to interpret the data. When the query accesses the `emp_type` type, Oracle checks the `EXECUTE` privilege of the user.

Execution of the second query, however, does not involve named types, so Oracle does not check type privileges.

In addition, by using the schema from the previous section, `user3` can perform the following queries:

    SELECT tab1.col1.attr2 FROM user2.tab1 tab1;
    SELECT attr4.attr3.attr2 FROM tab3;

Note that in both `SELECT` statements, `user3` does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the `GRANT` `OPTION.`

Oracle Database checks privileges on the following events and returns an error if the client does not have the privilege for the action:

- Pinning an object in the object cache using its `REF` value causes Oracle Database to check for the `SELECT` privilege on the containing object table.

- Modifying an existing object or flushing an object from the object cache causes Oracle Database to check for the `UPDATE` privilege on the destination object table.

- Flushing a new object causes Oracle Database to check for the `INSERT` privilege on the destination object table.

- Deleting an object causes Oracle Database to check for the `DELETE` privilege on the destination table.

- Pinning an object of a named type causes Oracle to check `EXECUTE` privilege on the object.

Modifying the attributes of an object in a client third-generation language application causes Oracle Database to update the entire object. Therefore, the user needs `UPDATE` privilege on the object table. Having the `UPDATE` privilege on only certain columns of the object table is not sufficient, even if the application only modifies attributes corresponding to those columns. Therefore, Oracle Database does not support column-level privileges for object tables.

#### 5.1.6.7 Type Dependencies

As with stored objects, such as procedures and tables, types being referenced by other objects are called dependencies. There are some special issues for types that tables depend on. Because a table contains data that relies on the type definition for access, any change to the type causes all stored data to become inaccessible. Changes that can cause this are when necessary privileges required by the type are revoked or the type or dependent types are dropped. If either of these actions occur, then the table becomes invalid and cannot be accessed.

A table that is invalid because of missing privileges can automatically become valid and accessible if the required privileges are granted again. A table that is invalid because a dependent type has been dropped can never be accessed again, and the only permissible action is to drop the table.

Because of the severe effects that revoking a privilege on a type or dropping a type can cause, the SQL statements `REVOKE` and `DROP` `TYPE`, by default, implement a restrict semantics. This means that if the named type in either statement has table or type dependents, then an error is received and the statement aborts. However, if the `FORCE` clause for either statement is used, then the statement always succeeds. If there are depended-upon tables, then they are invalidated.

See Also: Oracle Database Reference for details about using the `REVOKE`, `DROP` `TYPE`, and `FORCE` clauses

## 5.2 Introduction to Roles

Managing and controlling privileges is made easier by using roles, which are named groups of related privileges that you grant as a group to users or other roles. Within a database, each role name must be unique, different from all user names and all other role names. Unlike schema objects, roles are not contained in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.

Roles are designed to ease the administration of an end-user system and schema object privileges and are often maintained in Oracle Internet Directory. However, roles are not meant to be used by application developers, because the privileges to access schema objects within stored programmatic constructs need to be granted directly.

The effective management of roles is discussed in the following subsections:

Authentication Considerations in These Topical Areas | Links to Relevant Subsection
--- | ---
Why Roles Are Advantageous | Properties of Roles
How Roles are Typically Used | Common Uses of Roles
How Users Get Roles (or Role Restrictions | Granting and Revoking Roles
How Roles Affect The Scope of a User's Privileges | Security Domains of Roles and Users
How Roles Work in PL/SQL Blocks | PL/SQL Blocks and Roles
How Roles Aid or Restrict DDL Usage | DDL Statements and Roles
What Roles are Predefined in Oracle | Predefined Roles
How Can Operating Systems Aid Roles | Operating System and Roles
How Roles Work in a Remote Session | Roles in a Distributed Environment
How Secure Application Roles Are Created and Used | Secure Application Roles

### 5.2.1 Properties of Roles

Table 5-3 discusses the properties of roles that enable easier privilege management within a database:

Table 5-3 Properties of Roles and Their Description

Property | Description
--- | ---
Reduced privilege administration | Rather than granting the same set of privileges explicitly to several users, you can grant the privileges for a group of related users to a role, and then only the role needs to be granted to each member of the group.
Dynamic privilege management | If the privileges of a group must change, then only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
Selective availability of privileges | You can selectively enable or disable the roles granted to a user. This allows specific control of a user's privileges in any given situation.
Application awareness | The data dictionary records which roles exist, so you can design applications to query the dictionary and automatically enable (or disable) selective roles when a user attempts to execute the application by way of a given user name.
Application-specific security | You can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password.

Database administrators often create roles for a database application. The DBA grants a secure application role all privileges necessary to run the application. The DBA then grants the secure application role to other roles or users. An application can have several different roles, each granted a different set of privileges that allow for more or less data access while using the application.

The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role. Typically, an application is designed so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application role.

See Also: "DDL Statements and Roles" for information about restrictions for procedures Oracle Database Application Developer's Guide - Fundamentals for instructions for enabling roles from an application

### 5.2.2 Common Uses of Roles

In general, you create a role to serve one of two purposes:

- To manage the privileges for a database application (Application Roles)

- To manage the privileges for a user group (User Roles)

Figure 5-1 and the sections that follow describe the two uses of roles.

Figure 5-1 Common Uses for Roles

Description of the illustration cncpt082.gif

#### 5.2.2.1 Application Roles

You grant an application role all privileges necessary to run a given database application. Then, you grant the secure application role to other roles or to specific users. An application can have several different roles, with each role assigned a different set of privileges that allow for more or less data access while using the application.

Note: Grants of password protection or application roles to another role will not be allowed in future Oracle Database releases.

#### 5.2.2.2 User Roles

You create a user role for a group of database users with common privilege requirements. You manage user privileges by granting secure application roles and privileges to the user role and then granting the user role to appropriate users.

### 5.2.3 Granting and Revoking Roles

System or schema object privileges can be granted to a role, and any role can be granted to any database user or to another role (but not to itself). However, a role cannot be granted circularly, that is, a role `X` cannot be granted to role `Y` if role `Y` has previously been granted to role `X`.

To provide selective availability of privileges, Oracle Database allows applications and users to enable and disable roles. Each role granted to a user is, at any given time, either enabled or disabled. The security domain of a user includes the privileges of all roles currently enabled for the user and excludes the privileges of any roles currently disabled for the user.

A role granted to a role is called an indirectly granted role. It can be explicitly enabled or disabled for a user. However, whenever you enable a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

You grant roles to (or revoke roles from) users or other roles by using either of the following methods:

- Oracle Enterprise Manager 10g Database Control

- The SQL statements, `GRANT` and `REVOKE`

Privileges are granted to and revoked from roles using the same options. Roles can also be granted to and revoked from users using the operating system that runs Oracle, or through network services.

See Also: For more information about Database Control, see Oracle Database 2 Day DBA Modifying users, roles, or privileges with the Database Control, see the Enterprise Manager online help

#### 5.2.3.1 Who Can Grant or Revoke Roles?

Any user with the `GRANT` `ANY` `ROLE` system privilege can grant or revoke any role except a global role to or from other users or roles of the database. You should grant this system privilege conservatively because it is very powerful.

Any user granted a role with the `ADMIN` `OPTION` can grant or revoke that role to or from other users or roles of the database. This option allows administrative powers for roles on a selective basis.

See Also: Oracle Database Administrator's Guide for information about global roles

### 5.2.4 Security Domains of Roles and Users

Each role and user has its own unique security domain. The security domain of a role includes the privileges granted to the role plus those privileges granted to any roles that are granted to the role.

The security domain of a user includes privileges on all schema objects in the corresponding schema, the privileges granted to the user, and the privileges of roles granted to the user that are currently enabled. (A role can be simultaneously enabled for one user and disabled for another.) This domain also includes the privileges and roles granted to the user group `PUBLIC`.

### 5.2.5 PL/SQL Blocks and Roles

The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.

#### 5.2.5.1 Named Blocks with Definer's Rights

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

The `SESSION_ROLES` view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries `SESSION_ROLES`, then the query does not return any rows.

See Also: Oracle Database Reference

#### 5.2.5.2 Anonymous Blocks with Invoker's Rights

Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block, and you can use dynamic SQL to set a role in the session.

See Also: PL/SQL User's Guide and Reference for an explanation of invoker's and definer's rights "Dynamic SQL in PL/SQL" in Oracle Database Concepts

### 5.2.6 DDL Statements and Roles

A user requires one or more privileges to successfully execute a DDL statement, depending on the statement. For example, to create a table, the user must have the `CREATE` `TABLE` or `CREATE` `ANY` `TABLE` system privilege. To create a view of a table that belongs to another user, the creator requires the `CREATE VIEW` or `CREATE` `ANY` `VIEW` system privilege and either the `SELECT` `object` privilege for the table or the `SELECT` `ANY` `TABLE` system privilege.

Oracle Database avoids the dependencies on privileges received by way of roles by restricting the use of specific privileges in certain DDL statements. The following rules outline these privilege restrictions concerning DDL statements:

- All system privileges and schema object privileges that permit a user to perform a DDL operation are usable when received through a role. For example:

  - System Privileges: `CREATE` `TABLE`, `CREATE` `VIEW,` and `CREATE` `PROCEDURE` privileges

  - Schema Object Privileges: `ALTER` and `INDEX` privileges for a table

  Exception: The `REFERENCES` object privilege for a table cannot be used to define the foreign key of a table if the privilege is received through a role.

- All system privileges and object privileges that allow a user to perform a DML operation that is required to issue a DDL statement are not usable when received through a role. For example:

  - A user who receives the `SELECT` `ANY` `TABLE` system privilege or the `SELECT` `object` privilege for a table through a role can use neither privilege to create a view on a table that belongs to another user.

The following example further clarifies the permitted and restricted uses of privileges received through roles.

Assume that a user is:

- Granted a role that has the `CREATE` `VIEW` system privilege

- Granted a role that has the `SELECT` `object` privilege for the `employees` table, but the user is indirectly granted the `SELECT` `object` privilege for the `employees` table

- Directly granted the `SELECT` `object` privilege for the `departments` table

Given these directly and indirectly granted privileges:

- The user can issue `SELECT` statements on both the `employees` and `departments` tables.

- Although the user has both the `CREATE` `VIEW` and `SELECT` privilege for the `employees` table through a role, the user cannot create a usable view on the `employees` table, because the `SELECT` `object` privilege for the `employees` table was granted through a role. Any views created will produce errors when accessed.

- The user can create a view on the `departments` table, because the user has the `CREATE` `VIEW` privilege through a role and the `SELECT` privilege for the `departments` table directly.

### 5.2.7 Predefined Roles

The following roles are defined automatically for Oracle Database:

- `CONNECT`

- `RESOURCE`

- `DBA`

- `EXP_FULL_DATABASE`

- `IMP_FULL_DATABASE`

These roles are provided for backward compatibility to earlier versions of Oracle Database and can be modified in the same manner as any other role in an Oracle database.

Note: Each installation should create its own roles and assign only those privileges that are needed, thus retaining detailed control of the privileges in use. This process also removes any need to adjust existing roles, privileges, or procedures whenever Oracle Database changes or removes roles that Oracle Database defines. For example, the `CONNECT` role now has only one privilege: `CREATE SESSION`. Both `CONNECT` and `RESOURCE` roles will be deprecated in future Oracle versions.

### 5.2.8 Operating System and Roles

In some environments, you can administer database security using the operating system. The operating system can be used to manage the granting (and revoking) of database roles and to manage their password authentication. This capability is not available on all operating systems.

See Also: Your operating system specific Oracle documentation for details on managing roles through the operating system

### 5.2.9 Roles in a Distributed Environment

When you use roles in a distributed database environment, you must ensure that all needed roles are set as the default roles for a distributed (remote) session. These roles cannot be enabled when you connect to a remote database from within a local database session. For example, you cannot execute a remote procedure that attempts to enable a role at the remote site.

See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide

### 5.2.10 Secure Application Roles

Oracle Database provides secure application roles, which are roles that can only be enabled by authorized PL/SQL packages. This mechanism restricts the enabling of such roles to the invoking application.

Security is strengthened when passwords are not embedded in application source code or stored in a table. Instead, a secure application role can be created, specifying which PL/SQL package is authorized to enable the role. Package identity is used to determine whether privileges are sufficient to enable the roles. Before enabling the role, the application can perform authentication and customized authorization, such as checking whether the user has connected through a proxy.

Note: Because of the restriction that users cannot change the security domain inside definer's right procedures, secure application roles can only be enabled inside invoker's right procedures.

#### 5.2.10.1 Creation of Secure Application Roles

Secure application roles are created by using the statement `CREATE` `ROLE` ... `IDENTIFIED` `USING`. Here is an example:

    CREATE ROLE admin_role IDENTIFIED USING hr.admin;

This statement indicates the following:

- The role `admin_role` to be created is a secure application role.

- The role can only be enabled by modules defined inside the PL/SQL package `hr`.`admin`.

You must have the `CREATE` `ROLE` system privilege to execute this statement.

When such a role is assigned to a user, it becomes a default role for that user, which is automatically enabled at login without resorting to the package. A user with a default role does not have to be authenticated in any way to use the role. For example, the password for the role is not requested or required.

To restrict the role solely to the use specified by the `IDENTIFIED USING` clause, you can take either of the following actions:

- Immediately after granting such a role to a user, issue an `ALTER USER` statement with the clause `DEFAULT ROLE ALL EXCEPT` `role`, substituting the application role for `role`. Then `role` can only be used by applications executing the authorized package.

- When assigning roles, use GRANT ALL EXCEPT `role`.

Roles that are enabled inside an invoker's right procedure remain in effect even after the procedure exits. Therefore, you can have a dedicated procedure that deals with enabling the role for the rest of the session to use.

See Also: Oracle Database SQL Reference PL/SQL User's Guide and Reference PL/SQL Packages and Types Reference Oracle Database Application Developer's Guide - Fundamentals

## 5.3 User Resource Limits

You can set limits on the amount of various system resources available to each user as part of the security domain of that user. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

This resource limit feature is very useful in large, multiuser systems, where system resources are very expensive. Excessive consumption of these resources by one or more users can detrimentally affect the other users of the database. In single-user or small-scale multiuser database systems, the system resource feature is not as important, because user consumption of system resources is less likely to have detrimental impact.

You manage user resource limits by means of Database Resource Manager. You can set password management preferences using profiles, either set individually or using a default profile for many users. Each Oracle database can have an unlimited number of profiles. Oracle allows the security administrator to enable or disable the enforcement of profile resource limits universally.

See Also: For resource management, see Database Resource Manager For passwords, see Password Management Policy

Setting resource limits causes a slight performance degradation when users create sessions, because Oracle loads all resource limit data for each user upon each connection to the database.

See Also: Oracle Database Administrator's Guide for information about security administrators

Resource limits and profiles are discussed in the following sections:

- Types of System Resources and Limits

- Profiles

### 5.3.1 Types of System Resources and Limits

Oracle Database can limit the use of several types of system resources, including CPU time and logical reads. In general, you can control each of these resources at the session level, call level, or both, as discussed in the following subsections:

- Session Level

- Call Level

- CPU Time

- Logical Reads

- Limiting Other Resources

#### 5.3.1.1 Session Level

Each time a user connects to a database, a session is created. Each session consumes CPU time and memory on the computer that runs Oracle Database. You can set several resource limits at the session level.

If a user exceeds a session-level resource limit, then Oracle terminates (rolls back) the current statement and returns a message indicating that the session limit has been reached. At this point, all previous statements in the current transaction are intact, and the only operations the user can perform are `COMMIT`, `ROLLBACK`, or disconnect (in this case, the current transaction is committed). All other operations produce an error. Even after the transaction is committed or rolled back, the user can accomplish no more work during the current session.

#### 5.3.1.2 Call Level

Each time a SQL statement is executed, several steps are taken to process the statement. During this processing, several calls are made to the database as a part of the different execution phases. To prevent any one call from using the system excessively, Oracle Database lets you set several resource limits at the call level.

If a user exceeds a call-level resource limit, then Oracle Database halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user session remains connected.

#### 5.3.1.3 CPU Time

When SQL statements and other types of calls are made to Oracle Database, a certain amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing.

To prevent uncontrolled use of CPU time, you can set fixed or dynamic limits on the CPU time for each call and the total amount of CPU time used for Oracle calls during a session. The limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.

#### 5.3.1.4 Logical Reads

I/O is one of the most expensive operations in a database system. SQL statements that are I/O-intensive can monopolize memory and disk use and cause other database operations to compete for these resources.

To prevent single sources of excessive I/O, Oracle Database lets you limit the logical data block reads for each call and for each session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or during a session.

#### 5.3.1.5 Limiting Other Resources

Oracle Database also provides for limiting several other resources at the session level:

- You can limit the number of concurrent sessions for each user. Each user can create only up to a predefined number of concurrent sessions.

- You can limit the idle time for a session. If the time between calls in a session reaches the idle time limit, then the current transaction is rolled back, the session is aborted, and the resources of the session are returned to the system. The next call receives an error that indicates that the user is no longer connected to the instance. This limit is set as a number of elapsed minutes.

  Note: Shortly after a session is aborted because it has exceeded an idle time limit, the process monitor (PMON) background process cleans up after the aborted session. Until PMON completes this process, the aborted session is still counted in any session or user resource limit.

- You can limit the elapsed connect time for each session. If the duration of a session exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.

  Note: Oracle Database does not constantly monitor the elapsed idle time or elapsed connection time. Doing so would reduce system performance. Instead, it checks every few minutes. Therefore, a session can exceed this limit slightly (for example, by five minutes) before Oracle enforces the limit and aborts the session.

- You can limit the amount of private System Global Area (SGA) space (used for private SQL areas) for a session. This limit is only important in systems that use the shared server configuration. Otherwise, private SQL areas are located in the Program Global Area (PGA). This limit is set as a number of bytes of memory in the SGA of an instance. Use the characters K or M to specify kilobytes or megabytes.

  See Also: For instructions on enabling or disabling resource limits: Viewing Information About Database Users and Profiles Managing User Roles Oracle Database Administrator's Guide

## 5.4 Profiles

In general, the word profile refers to a collection of attributes that apply to a user, enabling a single point of reference for any of multiple users that share those exact attributes. User profiles in Oracle Internet Directory contain a wide range of attributes pertinent to directory usage and authentication for each user. Similarly, profiles in Oracle Label Security contain attributes useful in label security user administration and operations management. Profile attributes can include restrictions on system resources, but for that purpose Database Resource Manager is preferred.

See Also: For resources, see discussion of the Database Resource Manager in the Oracle Database Administrator's Guide For viewing resource information, see Viewing Information About Database Users and Profiles For password policies, see Password Management Policy in Chapter 7, "Security Policies"

### 5.4.1 Determining Values for Resource Limits

Before creating profiles and setting the resource limits associated with them, you should determine appropriate values for each resource limit. You can base these values on the type of operations a typical user performs. For example, if one class of user does not normally perform a high number of logical data block reads, then set the `LOGICAL_READS_PER_SESSION` and `LOGICAL_READS_PER_CALL` limits conservatively.

Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage. For example, the database or security administrator can use the `AUDIT` `SESSION` clause to gather information about the limits `CONNECT_TIME`, `LOGICAL_READS_PER_SESSION`, and `LOGICAL_READS_PER_CALL`.

You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.

See Also: Chapter 8, "Database Auditing: Security Considerations" For more information about the Database Control, see Oracle Database 2 Day DBA For information about the Monitor feature, see the Enterprise Manager online help